const connection = require("../app/database");

class MomentService {
  async create(content, userId) {
    const statement = "INSERT INTO `moment` (content,user_id) VALUES (?,?)";
    const [result] = await connection.execute(statement, [content, userId]);
    return result;
  }

  async queryList(offset, size) {
    const statement = `SELECT m.id id,m.content content,m.createAt crateTime,m.updateAt updateTime,
      (SELECT COUNT(*) FROM comment WHERE moment_id = m.id) momentCount,
      JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt)user FROM moment as m LEFT JOIN users as u ON m.user_id = u.id  LIMIT ? OFFSET ?`;

    const [result] = await connection.execute(statement, [String(size), String(offset)]);

    return result;
  }
  async queryById(id) {
    const statement = `
    SELECT m.id id,
    m.content content,
    m.createAt crateTime,
    m.updateAt updateTime,
    JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt) user,
    (JSON_ARRAYAGG(JSON_OBJECT('id',c.id,'content',c.content,'commentId',c.comment_id,'user',JSON_OBJECT('id',cu.id,'name',cu.name)))) commens
    FROM moment as m
    LEFT JOIN users as u ON m.user_id = u.id 
    LEFT JOIN comment as c ON  c.moment_id = m.id
    LEFT JOIN users as cu ON cu.id = c.user_id
    WHERE c.moment_id = ?
    GROUP BY m.id
      `;

    const [result] = await connection.execute(statement, [id]);

    return result;
  }

  async update(content, id) {
    const statement = "UPDATE `moment` SET `content` = ? WHERE id =?";
    const [result] = await connection.execute(statement, [content, id]);
    return result;
  }
  async deleteById(id) {
    const statement = "DELETE  FROM moment WHERE id = ?";
    const [result] = await connection.execute(statement, [id]);
    return result;
  }

  
}

module.exports = new MomentService();
